﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using Lead2012.Models;
using Lead2012.ViewModels;
using Lead2012.Helpers;
using Lead2012.Business;
using System.Data;
using System.Data.SqlClient;

namespace Lead2012.Repository
{
    public class LeadWarehouseManagementRepository : BaseManagementRepository
    {
        // Create an instance of entities framework
        Lead2012Entities entities = new Lead2012Entities();

        #region Build Models
        public void GetModel(ref LeadViewModel model, int id)
        {
            model.UserFilter = GetAllUsers();
            model.LeadSource = GetLeadSource();
            model.LeadSource.RemoveAt(0);
            model.LeadType = GetLeadType();
            model.LeadType.RemoveAt(0);
            model.ListTab = new FieldManagementRepository().GetFieldGroup();
            model.StatusFilter = GetStatuses();
            model.StatusFilter.RemoveAt(0);
            model.StatusLogList = GetAllLeadStatusLogItems(id);
            model.ListAppointment = new LeadAppointmentRepository().GetAppoimentModel(id);

            // Assign information to lead
            // First:   Get information from lead
            var leadInfo = GetLeadWarehouseInformation(id);
            model.LeadSourceId = leadInfo[3].ColumnValue;
            model.LeadTypeId = leadInfo[8].ColumnValue;
            model.DateAdd = Convert.ToDateTime(leadInfo[9].ColumnValue).ToString("dd-MM-yyyy hh:mm:ss tt");
            model.Status = model.StatusFilter.Where(c => c.ID.Equals(leadInfo[2].ColumnValue)).FirstOrDefault().ItemName;
            model.OwnerId = leadInfo[1].ColumnValue;
            model.StatusId = leadInfo[2].ColumnValue;

            // Get Lead Name
            var listFieldSystem = new FieldSystemRepository().GetAll();
            if (listFieldSystem != null && listFieldSystem.Count > 0)
            {
                var fieldNames = listFieldSystem.Where(c => c.TextColumnName.Equals("Name", StringComparison.OrdinalIgnoreCase)).ToList();
                if (fieldNames != null && fieldNames.Count > 0)
                {
                    string leadName = string.Empty;
                    for (int i = 0; i < fieldNames.Count; i++)
                    {
                        var fieldName = string.Format("f{0}", fieldNames[i].FieldId);
                        var fieldModel = leadInfo.Where(c => c.ColumnName.Equals(fieldName, StringComparison.OrdinalIgnoreCase)).First();
                        if (fieldModel != null)
                        {
                            if (i == 0)
                                leadName += string.Format("{0}", fieldModel.ColumnValue);
                            else
                                leadName += string.Format(" {0}", fieldModel.ColumnValue);
                        }
                    }
                    model.LeadName = leadName;
                }
            }

            // Second:  Lookup information on list
            //          Assign each field info     
            LeadMappingWithFields(ref model, leadInfo);
        }
        
        public void GetModelPrint(ref LeadViewModel model, int id)
        {
            model.UserFilter = GetAllUsers();
            model.LeadSource = GetLeadSource();
            model.LeadSource.RemoveAt(0);
            model.LeadType = GetLeadType();
            model.LeadType.RemoveAt(0);
            model.ListTab = new FieldManagementRepository().GetFieldGroupForPrint();
            model.StatusFilter = GetStatuses();
            model.StatusFilter.RemoveAt(0);

            // Assign information to lead
            // First:   Get information from lead
            var leadInfo = GetLeadWarehouseInformation(id);
            model.LeadSourceId = leadInfo[3].ColumnValue;
            model.LeadTypeId = leadInfo[8].ColumnValue;
            model.DateAdd = Convert.ToDateTime(leadInfo[9].ColumnValue).ToString("dd-MM-yyyy hh:mm:ss tt");
            model.Status = model.StatusFilter.Where(c => c.ID.Equals(leadInfo[2].ColumnValue)).FirstOrDefault().ItemName;
            model.OwnerId = leadInfo[1].ColumnValue;
            model.StatusId = leadInfo[2].ColumnValue;

            // Get Lead Name
            var listFieldSystem = new FieldSystemRepository().GetAll();
            if (listFieldSystem != null && listFieldSystem.Count > 0)
            {
                var fieldNames = listFieldSystem.Where(c => c.TextColumnName.Equals("Name", StringComparison.OrdinalIgnoreCase)).ToList();
                if (fieldNames != null && fieldNames.Count > 0)
                {
                    string leadName = string.Empty;
                    for (int i = 0; i < fieldNames.Count; i++)
                    {
                        var fieldName = string.Format("f{0}", fieldNames[i].FieldId);
                        var fieldModel = leadInfo.Where(c => c.ColumnName.Equals(fieldName, StringComparison.OrdinalIgnoreCase)).First();
                        if (fieldModel != null)
                        {
                            if (i == 0)
                                leadName += string.Format("{0}", fieldModel.ColumnValue);
                            else
                                leadName += string.Format(" {0}", fieldModel.ColumnValue);
                        }
                    }
                    model.LeadName = leadName;
                }
            }

            // Second:  Lookup information on list
            //          Assign each field info     
            LeadMappingWithFields(ref model, leadInfo);
        }
        public void GetModel(ref LeadViewModel model)
        {
            model.UserFilter = GetAllUsers();
            model.LeadSource = GetLeadSource();
            model.LeadType = GetLeadType();
            model.ListTab = new FieldManagementRepository().GetFieldGroup();
        }

        #region Clear Input tags
        public void ClearInputTags(ref LeadViewModel model, int leadFormTypeId)
        {
            // Looking up on all fields
            // Get only common fields and fields of leadformtype
            RemoveUnnecessaryItems(model.ListTab, leadFormTypeId);

        }
        private static void RemoveUnnecessaryItems(IList<FieldGroupInfo> tabs, int? leadFormTypeId)
        {
            int i = 0;
            while (i < tabs.Count)
            {
                if (tabs[i].Groups != null && tabs[i].Groups.Count > 0)
                {
                    RemoveUnnecessaryItems(tabs[i].Groups, leadFormTypeId);
                }

                if (tabs[i].Fields != null && tabs[i].Fields.Count > 0)
                {
                    tabs[i].Fields.RemoveAll(f => f == null
                        || (f.LeadFormTypes != null && f.LeadFormTypes.Count > 0
                            && (leadFormTypeId == null || !f.LeadFormTypes.Exists(ft => ft.Id == leadFormTypeId))));
                }

                if ((tabs[i].Groups == null || tabs[i].Groups.Count == 0)
                    && (tabs[i].Fields == null || tabs[i].Fields.Count == 0))
                {
                    tabs.RemoveAt(i);
                }
                else i++;
            }
        }
        #endregion


        // Build Functions
        private void LeadMappingWithFields(ref LeadViewModel model, List<FieldNameValue> list)
        {
            if (model != null && model.ListTab != null && model.ListTab.Count > 0)
            {
                // 1.0
                for (int i = 0; i < model.ListTab.Count; i++)
                {
                    // 2.0
                    // Loop on fields of group
                    if (model.ListTab[i] != null && model.ListTab[i].Fields != null && model.ListTab[i].Fields.Count > 0)
                    {
                        for (int j = 0; j < model.ListTab[i].Fields.Count; j++)
                        {
                            string fieldId = string.Format("f{0}", model.ListTab[i].Fields[j].Id);
                            model.ListTab[i].Fields[j].Value = list.Where(c => c.ColumnName.Equals(fieldId)).First().ColumnValue;                            
                            if (model.ListTab[i].Fields[j].TypeId == 32 || model.ListTab[i].Fields[j].TypeId == 64 || model.ListTab[i].Fields[j].TypeId == 1024)
                            {                                
                                // Get list field items
                                var listFieldItems = new FieldItemManagementRepository().GetAllItemByField(model.ListTab[i].Fields[j].Id);
                                model.ListTab[i].Fields[j].DataSource = new FieldManagementRepository().GetAllFieldItemsByField(model.ListTab[i].Fields[j], listFieldItems);
                            }
                        }
                    }

                    // 3.0
                    // Loop on fields of group/section
                    if (model.ListTab[i].Groups != null && model.ListTab[i].Groups.Count > 0)
                    {
                        for (int k = 0; k < model.ListTab[i].Groups.Count; k++)
                        {
                            // 4.0
                            // Loop on fields of section
                            if (model.ListTab[i].Groups[k].Fields != null && model.ListTab[i].Groups[k].Fields.Count > 0)
                            {
                                for (var l = 0; l < model.ListTab[i].Groups[k].Fields.Count; l++)
                                {
                                    string fieldSectionId = string.Format("f{0}", model.ListTab[i].Groups[k].Fields[l].Id);
                                    model.ListTab[i].Groups[k].Fields[l].Value = list.Where(c => c.ColumnName.Equals(fieldSectionId)).First().ColumnValue;
                                    if (model.ListTab[i].Groups[k].Fields[l].TypeId == 32 || model.ListTab[i].Groups[k].Fields[l].TypeId == 64 || model.ListTab[i].Groups[k].Fields[l].TypeId == 1024)
                                    {
                                        // Get list field items
                                        var listFieldItems = new FieldItemManagementRepository().GetAllItemByField(model.ListTab[i].Groups[k].Fields[l].Id);
                                        model.ListTab[i].Groups[k].Fields[l].DataSource = new FieldManagementRepository().GetAllFieldItemsByField(model.ListTab[i].Groups[k].Fields[l], listFieldItems);
                                    }
                                }
                            }
                        }
                    }
                }
            }
        }

        public List<LeadStatusLongItem> GetAllLeadStatusLogItems(int leadId)
        {
            List<LeadStatusLongItem> list = new List<LeadStatusLongItem>();

            var items = (from c in entities.LeadStatusLogs
                         join a in entities.LeadStatuses on c.StatusId equals a.StatusId
                         where c.LeadId == leadId
                         orderby c.LogId descending
                         select new
                         {
                             Id = c.LogId,
                             LogDate = c.LogDate,
                             Title = a.Title,
                             Note = c.Note
                         }).ToList();

            // Add item to list
            foreach (var item in items)
            {
                LeadStatusLongItem model = new LeadStatusLongItem();
                model.DateDecs = item.LogDate.ToString("dd-MM-yyyy hh:mm:ss tt");
                model.Detail = item.Note;
                model.Title = item.Title;
                list.Add(model);
            }


            return list;
        }

        #endregion

        #region Public methods
        public void AddColumn(string fieldName)
        {
            string sqlQuery = string.Format("ALTER TABLE LeadWarehouse ADD {0} Nvarchar(800)", fieldName);
            int value = SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionStringProfile, CommandType.Text, sqlQuery);
        }
        public void DeleteColumn(string fieldName)
        {
            string sqlQuery = string.Format("ALTER TABLE LeadWarehouse DROP COLUMN {0}", fieldName);
            int value = SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionStringProfile, CommandType.Text, sqlQuery);
        }
        public void DeleteLead(int leadId)
        {
            string sqlQuery = string.Format("DELETE [LeadWarehouse] WHERE [LeadId]={0}", leadId);
            int value = SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionStringProfile, CommandType.Text, sqlQuery);
        }
        public void ReassignLead(int leadId, int userId)
        {
            // Update owner id
            string sqlQuery = string.Format("UPDATE [LeadWarehouse] SET [UserId]={0} WHERE [LeadId]={1}", userId, leadId);
            int value = SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionStringProfile, CommandType.Text, sqlQuery);

            // Create a notification lead log
            try
            {
                NotificationResponseLogRepository repository = new NotificationResponseLogRepository();
                NotificationResponseLog log = new NotificationResponseLog();
                log.LeadId = leadId;
                log.LogDate = DateTime.Now;
                log.Response = false;
                log.UserId = userId;
                repository.Create(log);
            }
            catch
            {
            }
        }

        // Get a lead information
        public List<FieldNameValue> GetLeadWarehouseInformation(int leadId)
        {
            List<FieldNameValue> list = new List<FieldNameValue>();
            string query = string.Format("SELECT * FROM [LeadWarehouse] WHERE [LeadId]={0}", leadId);

            // Execute query to get list of column name on leadwarehouse table
            using (SqlDataReader item = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringProfile, CommandType.Text, query))
            {
                if (item.Read())
                {
                    // Get all field column
                    // With an column, we will add as a record (item) for list
                    for (int i = 0; i < item.FieldCount; i++)
                    {
                        FieldNameValue model = new FieldNameValue();

                        model.ColumnName = item.GetName(i);
                        if (i < 9)
                        {
                            if (!item.IsDBNull(i))
                                model.ColumnValue = item.GetInt32(i).ToString();
                            else
                                model.ColumnValue = string.Empty;
                        }
                        else if (i < 12)
                        {
                            if (!item.IsDBNull(i))
                                model.ColumnValue = item.GetDateTime(i).ToString();
                            else
                                model.ColumnValue = string.Empty;
                        }
                        else
                        {
                            if (!item.IsDBNull(i))
                                model.ColumnValue = item.GetString(i).ToString();
                            else
                                model.ColumnValue = string.Empty;
                        }
                        list.Add(model);
                    }
                }
            }

            return list;
        }

        public int Create(int userId, int statusId, int sourceId, int milestoneId, int lastActionTypeId, int leadFormTypeId, FormCollection form)
        {
            // First: Get all column names
            var columns = GetAllColumnName();

            // Second: Make an insert clause to add a new lead
            string sqlQuery = string.Empty;
            sqlQuery = "INSERT INTO [LeadWarehouse] ";
            sqlQuery += string.Format(" VALUES({0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, N'{8}', N'{9}', N'{10}'", userId, statusId, sourceId, milestoneId, 0, 0, lastActionTypeId, leadFormTypeId, DateTime.Now, DateTime.Now, DateTime.Now);
            
            for (int i = 12; i < columns.Count; i++)
            {
                string fieldValue = (form[columns[i].Replace("f", string.Empty)] == null) ? string.Empty : form[columns[i].Replace("f", string.Empty)].ToString();
                sqlQuery += string.Format(", N'{0}'", fieldValue);
            }
            sqlQuery += ")";

            // Execute none query to insert data
            int value = SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionStringProfile, CommandType.Text, sqlQuery);

            // Execute query to get lead id
            int leadId = 0;
            sqlQuery = "SELECT TOP 1 [LeadId] FROM [LeadWarehouse] ORDER BY [LeadId] DESC";
            using (SqlDataReader item = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringProfile, CommandType.Text, sqlQuery))
            {
                if (item.Read())
                {
                    leadId = item.GetInt32(0);
                }
            }

            return leadId;
        }

        public void Update(int leadId, int userId, int sourceId, int typeId, FormCollection form)
        {
            // First: Get all column names
            var columns = GetAllColumnName();

            // Second: Make an insert clause to add a new lead
            string sqlQuery = string.Empty;

            sqlQuery = string.Format("UPDATE [LeadWarehouse] SET [UserId]={0}, [SourceId]={1}, [LeadFormTypeId]={2}", userId, sourceId, typeId);                        
            for (int i = 12; i < columns.Count; i++)
            {
                string fieldValue = (form[columns[i].Replace("f", string.Empty)] == null)?string.Empty:form[columns[i].Replace("f", string.Empty)].ToString();
                sqlQuery += string.Format(" ,[{0}]=N'{1}'", columns[i], fieldValue);
            }
            sqlQuery += string.Format(" WHERE [LeadId]={0}", leadId);

            // Execute none query to insert data
            int value = SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionStringProfile, CommandType.Text, sqlQuery);
        }

        public void Update(int leadId, int statusId)
        {
            // First: Get all column names
            var columns = GetAllColumnName();

            // Second: Make an insert clause to add a new lead
            string sqlQuery = string.Empty;

            sqlQuery = string.Format("UPDATE [LeadWarehouse] SET [ActionCount]=[ActionCount]+1, [StatusId]={0} WHERE [LeadId]={1}", statusId, leadId);

            // Execute none query to insert data
            int value = SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionStringProfile, CommandType.Text, sqlQuery);
        }
        public void UpdateLeadMilestone(int leadId, int statusId, int milestoneId)
        {
            // First: Get all column names
            var columns = GetAllColumnName();

            // Second: Make an insert clause to add a new lead
            string sqlQuery = string.Empty;

            sqlQuery = string.Format("UPDATE [LeadWarehouse] SET [ActionCount]=[ActionCount]+1, [MilestoneId]={0}, [StatusId]={1}, [LastActionDate]='{2}' WHERE [LeadId]={3}", milestoneId, statusId, DateTime.Now, leadId);

            // Execute none query to insert data
            int value = SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionStringProfile, CommandType.Text, sqlQuery);
        }
        #endregion
   
    }
}